# -*- coding: UTF-8 -*-
import tushare as ts
import numpy as np
from datetime import date,datetime,timedelta
import MySQLdb
import json

import sys
reload(sys)
sys.setdefaultencoding('utf8')

#database manipulate functions
def insert_table(tbl, data):
    conn = MySQLdb.connect(host="localhost",user="admin",passwd="admin",charset="utf8")
    cursor = conn.cursor()
    conn.select_db('EADB')
    args = len(data[0])
    sql = "insert into "+str(tbl)+ " values (%s"+",%s"*(args-1)+")"
    cnt = 0
    for turple in data:
        try:
            cnt += cursor.execute(sql, turple)
            conn.commit()
        except MySQLdb.Error, e:
            try:
                sqlError = "MySQL Error %d:%s" % (e.args[0], e.args[1])
                print sqlError
            except IndexError:
                print "MySQL Error:%s" % str(e)
                conn.rollback()
    conn.close();
    return cnt

def update_table(tbl,col,colv,pk,pkv):
    conn = MySQLdb.connect(host="localhost",user="admin",passwd="admin",charset="utf8")
    cursor = conn.cursor()
    conn.select_db('EADB');
    try:
        sql = "update %s set %s=%%s where %s=%s" % (tbl, col, pk, pkv)
        # print sql
        rtn = cursor.execute(sql, [colv])
        conn.commit()
    except MySQLdb.Error, e:
        try:
            sqlError = "MySQL Error %d:%s" % (e.args[0], e.args[1])
            print sqlError
        except IndexError:
            print "MySQL Error:%s" % str(e)
        conn.rollback()
    #关闭连接，释放资源
    cursor.close()
    conn.close();
    return rtn

def query_table(tbl,col):
    conn = MySQLdb.connect(host="localhost",user="admin",passwd="admin",charset="utf8")
    cursor = conn.cursor()
    conn.select_db('EADB');
    try:
        sql = "select %s from %s" % (col,tbl)
        cursor.execute(sql)
        codes = cursor.fetchall()
        codes = list(codes)
    except MySQLdb.Error, e:
        try:
            sqlError = "MySQL Error %d:%s" % (e.args[0], e.args[1])
            print sqlError
        except IndexError:
            print "MySQL Error:%s" % str(e)
        conn.rollback()
    conn.close();
    return codes

def delete_stock_hist_data():
    conn = MySQLdb.connect(host="localhost",user="root",passwd="sudo",charset="utf8")
    cursor = conn.cursor()
    conn.select_db('EADB');
    try:
        sql = "truncate table stock_hist_data"
        cursor.execute(sql)
        sql = "update stock_basics set latestedInfo=NULL"
        rtn = cursor.execute(sql)
        conn.commit()
    except MySQLdb.Error, e:
        try:
            sqlError = "MySQL Error %d:%s" % (e.args[0], e.args[1])
            print sqlError
        except IndexError:
            print "MySQL Error:%s" % str(e)
        conn.rollback()
        rtn = -1
    finally:
        conn.close();
        return rtn

#get data functions
def get_stock_basics():
    print "getting stock_basics"
    print "     fetching data"
    stock_basics = ts.get_stock_basics()
    stock_basics = stock_basics.reset_index()
    print "     selecting cols"
    data = stock_basics.loc[:,["code", "name", "industry", "area", "pe", "pb", "outstanding", "timeToMarket"]].values
    data = list(data)
    for i in range(len(data)):
        data[i] = list(data[i])
        data[i].append(None)
        data[i][3] = str(data[i][3])
        if data[i][7] == 0:
            data[i][7] = None
    print "     inserting into database"
    rtn = insert_table("stock_basics",data)
    print "     updated "+str(rtn)+" rows in stock_basics"

def get_stock_hist_data():
    print "gettting stock_hist_data"
    codes = query_table("stock_basics", "code, name, timeToMarket, latestedInfo")
    total = 0
    for code in codes:
        try:
            #print code
            if code[2] is None:
                continue
            if code[3] is None:
                startTime = code[2]
            else:
                startTime = code[3]+timedelta(days=1)
            if startTime >= date.today():
                print ("    hist_data is up-to-date for stock "+code[0]+" ").encode()+code[1].encode("UTF-8")
                continue
            endTime = startTime+timedelta(days=365*10)
            if endTime >= date.today():
                endTime = date.today()-timedelta(days=1)
            print "     from "+str(startTime)+" to "+str(endTime)
            hist_data = ts.get_h_data(code=code[0],start=str(startTime),end=str(endTime))

            if hist_data is None:
                update_table("stock_basics", "latestedInfo", endTime, "code", code[0])
                continue
            hist_data = hist_data.reset_index()
            data = hist_data.loc[:,["date","open","close","high","low","volume","amount"]].values
            data = list(data)
            # latested = str(date.min)
            for i in range(len(data)):
                data[i] = list(data[i])
                # print data[i]
                data[i].insert(0, code[0])
                data[i][1] = str(data[i][1].date())
                # if data[i][1] > latested:
                #     latested = data[i][1]
            rtn = insert_table("stock_hist_data",data)
            total += rtn
            #print rtn
            update_table("stock_basics", "latestedInfo", endTime, "code", code[0])
            print ("\n    Updated "+str(rtn)+" rows for stock "+code[0]+" ").encode()+code[1].encode("UTF-8")
        except:
            print ("\n    No data for stock "+code[0]+" ").encode()+code[1].encode("UTF-8")
    print " updated totally "+str(total)+" rows for "+str(len(codes))+" stocks"
    return total

def get_index_basics():
    print "getting index_basics"
    data = [
    ["000001","上证指数","sh","19910715",None],
    ["399001","深证成指","sz","19940720",None],
    ["000300","沪深300","hs300","20041231",None],
    ["000016","上证50","sz50","20040102",None],
    ["399005","中小板指","zxb","20050607",None],
    ["399006","创业板指","cyb","20091030",None]
    ]
    rtn = insert_table("index_basics",data)
    print "     updated "+str(rtn)+" rows in index_basics"

def get_index_hist_data():
    print "gettting index_hist_data"
    codes = query_table("index_basics", "code,name,abbr,timeToMarket,latestedInfo")
    total = 0
    for code in codes:
        try:
            #print code[1].encode()
            if code[4] is None:
                startTime = code[3]
            else:
                startTime = code[4]+timedelta(days=1)
            endTime = startTime+timedelta(days=365)
            print "     from "+str(startTime)+" to "+str(endTime)
            hist_data = ts.get_h_data(code=code[0],index=True,start=str(startTime),end=str(endTime))
            #print "type:"+str(type(hist_data))
            hist_data = hist_data.reset_index()
            data = hist_data.loc[:,["date","open","close","high","low","volume","amount"]].values
            if len(data) == 0:
                print ("    index_data is up-to-date for stock "+code[0]+" ").encode()+code[1].encode("UTF-8")
                continue
            data = list(data)
            latested = str(date.min)
            for i in range(len(data)):
                data[i] = list(data[i])
                #print data[i]
                data[i].insert(0, code[0])
                data[i][1] = str(data[i][1].date())
                if data[i][1] > latested:
                    latested = data[i][1]
            rtn = insert_table("index_hist_data",data)
            total += rtn
            update_table("index_basics", "latestedInfo", latested, "code", code[0])
            print ("    Updated "+str(rtn)+" rows for index "+code[0]+" ").encode()+code[1].encode("UTF-8")
        except:
            print ("    No data for index "+code[0]+" ").encode()+code[1].encode("UTF-8")
    print " updated totally "+str(total)+" rows for "+str(len(codes))+" indexes"
    return total

def clean_stock_hist_data():
    print "cleaned "+str(delete_stock_hist_data())+" lines"

def get_calendar_data(code="600570"):
    conn = MySQLdb.connect(host="localhost",user="user",passwd="user",charset="utf8")
    cursor = conn.cursor()
    conn.select_db('EADB')
    sql = "call get_stk_hist(\"%s\");" % code
    print sql
    cursor.execute(sql)
    codes = cursor.fetchall()
    cursor.close()
    conn.close()
    codes = list(codes)
    for tpl_idx in range(len(codes)):
        codes[tpl_idx] = list(codes[tpl_idx])
        code = codes[tpl_idx]
        for idx in range(len(code)):
            if idx == 1:
                item = str("%.3f" % code[idx])
            else:
                item = str(code[idx])
            codes[tpl_idx][idx] = item
    return json.dumps(codes)

def get_beta_data(code = "600570"):
    conn = MySQLdb.connect(host="localhost",user="user",passwd="user",charset="utf8")
    cursor = conn.cursor()
    conn.select_db('EADB')
    sql = "call get_stk_beta_a(\"%s\");" % code
    print sql
    cursor.execute(sql)
    codes = cursor.fetchall()
    cursor.close()
    conn.close()
    codes = list(codes)
    for tpl_idx in range(len(codes)):
        codes[tpl_idx] = list(codes[tpl_idx])
        code = codes[tpl_idx]
        for idx in range(len(code)):
            if idx in [4,5,6,7]:
                item = str("%.3f" % code[idx])
                #print idx,item
                if len(item) < 6:
                    item = (6-len(item))*"&nbsp"+item;
            else:
                item = str(code[idx])
            codes[tpl_idx][idx] = item

    #print json.dumps(codes)
    return json.dumps(codes)

# def work():
#   df = ts.get_realtime_quotes('600570') #Single stock symbol
#   print df[['code','name','price','bid','ask','volume','amount','time']]
#
# def runTask(func, day=0, hour=0, min=0, second=0):
#   # Init time
#   now = datetime.now()
#   strnow = now.strftime('%Y-%m-%d %H:%M:%S')
#   print "now:",strnow
#   # First next run time
#   period = timedelta(days=day, hours=hour, minutes=min, seconds=second)
#   next_time = now + period
#   strnext_time = next_time.strftime('%Y-%m-%d %H:%M:%S')
#   print "next run:",strnext_time
#   while True:
#       # Get system current time
#       iter_now = datetime.now()
#       iter_now_time = iter_now.strftime('%Y-%m-%d %H:%M:%S')
#       if str(iter_now_time) == str(strnext_time):
#           # Get every start work time
#           print "start work: %s" % iter_now_time
#           # Call task func
#           func()
#           print "task done."
#           # Get next iteration time
#           iter_time = iter_now + period
#           strnext_time = iter_time.strftime('%Y-%m-%d %H:%M:%S')
#           print "next_iter: %s" % strnext_time
#           # Continue next iteration
#           continue

def update_index():
    total = 0
    updated = -1
    while updated != 0:
        total += updated
        updated = get_index_hist_data()
    return total
def update_stock():
    total = 0
    updated = -1
    while updated != 0:
        total += updated
        updated = get_stock_hist_data()
    return total

if __name__ == "__main__":
    #update_index()
    update_stock()
    #get_index_basics()
    #get_stock_basics()
    #print ts.get_h_data(code='000001',start='2015-12-19',end='2016-05-11')
    #print ts.get_hist_data("603999")
    #print ts.get_h_data("603999")
    #runTask(work,0,0,0,1)
    # code = ts.get_today_ticks(code="600570");
    # print code
